[MySQL] Optimize Query
Posted
by
bordeux
on Stack Overflow
See other posts from Stack Overflow
or by bordeux
Published on 2011-01-15T17:13:05Z
Indexed on
2011/01/15
17:53 UTC
Read the original article
Hit count: 196
Hello. I have problem with optimize this query:
SET @SEARCH = "dokumentalne";
SELECT SQL_NO_CACHE
`AA`.`version` AS `Version` ,
`AA`.`contents` AS `Contents` ,
`AA`.`idarticle` AS `AdressInSQL` ,
`AA` .`topic` AS `Topic` ,
MATCH (`AA`.`topic` , `AA`.`contents`) AGAINST (@SEARCH) AS `Relevance` ,
`IA`.`url` AS `URL`
FROM `xv_article` AS `AA`
INNER JOIN `xv_articleindex` AS `IA` ON ( `AA`.`idarticle` = `IA`.`adressinsql` )
INNER JOIN (
SELECT `idarticle` , MAX( `version` ) AS `version`
FROM `xv_article`
WHERE MATCH (`topic` , `contents`) AGAINST (@SEARCH)
GROUP BY `idarticle`
) AS `MG`
ON ( `AA`.`idarticle` = `MG`.`idarticle` )
WHERE `IA`.`accepted` = "yes"
AND `AA`.`version` = `MG`.`version`
ORDER BY `Relevance` DESC
LIMIT 0 , 30
Now, this query using ^ 20 seconds. How to optimize this?
EXPLAIN gives this:
1 PRIMARY AA ALL NULL NULL NULL NULL 11169 Using temporary; Using filesort 1 PRIMARY ALL NULL NULL NULL NULL 681 Using where 1 PRIMARY IA ALL accepted NULL NULL NULL 11967 Using where 2 DERIVED xv_article fulltext topic topic 0 1 Using where; Using temporary; Using filesort
This is example server with my data:
user: bordeux_4prog password: 4prog phpmyadmin: http://phpmyadmin.bordeux.net/ chive: http://chive.bordeux.net/
© Stack Overflow or respective owner